﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using System.Data.OleDb;

namespace Zhuang.Utility.Common.DataBase
{
    public abstract class OleDbHelper
    {
        //数据库连接字符串(web.config来配置)
        //public static string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+System.Environment.CurrentDirectory+"\\DB\\AppDB.accdb";
        //protected static string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|FOU.mdb;Persist Security Info=False;Jet OLEDB:Database Password=qazyq86qt5233";
        public static string connectionString = "";

		public OleDbHelper()
		{
		}
		public static int GetMaxID(string FieldName, string TableName)
		{
			string strsql = "select max(" + FieldName + ") from " + TableName;
			object obj = OleDbHelper.GetSingle(strsql);
			int result;
			if (obj == null)
			{
				result = 1;
			}
			else
			{
				result = int.Parse(obj.ToString());
			}
			return result;
		}
		public static bool Exists(string strSql)
		{
			object obj = OleDbHelper.GetSingle(strSql);
			int cmdresult;
			if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
			{
				cmdresult = 0;
			}
			else
			{
				cmdresult = int.Parse(obj.ToString());
			}
			return cmdresult != 0;
		}
		public static bool Exists(string strSql, params OleDbParameter[] cmdParms)
		{
			object obj = OleDbHelper.GetSingle(strSql, cmdParms);
			int cmdresult;
			if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
			{
				cmdresult = 0;
			}
			else
			{
				cmdresult = int.Parse(obj.ToString());
			}
			return cmdresult != 0;
		}
		public static int ExecuteSql(string SQLString)
		{
			int result;
			using (OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString))
			{
				using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
				{
					try
					{
						connection.Open();
						int rows = cmd.ExecuteNonQuery();
						result = rows;
					}
					catch (OleDbException E)
					{
						connection.Close();
						throw new Exception(E.Message);
					}
				}
			}
			return result;
		}
		public static void ExecuteSqlTran(ArrayList SQLStringList)
		{
			using (OleDbConnection conn = new OleDbConnection(OleDbHelper.connectionString))
			{
				conn.Open();
				OleDbCommand cmd = new OleDbCommand();
				cmd.Connection = conn;
				OleDbTransaction tx = conn.BeginTransaction();
				cmd.Transaction = tx;
				try
				{
					for (int i = 0; i < SQLStringList.Count; i++)
					{
						string strsql = SQLStringList[i].ToString();
						if (strsql.Trim().Length > 1)
						{
							cmd.CommandText = strsql;
							cmd.ExecuteNonQuery();
						}
					}
					tx.Commit();
				}
				catch (OleDbException E)
				{
					tx.Rollback();
					throw new Exception(E.Message);
				}
			}
		}
		public static int ExecuteSql(string SQLString, string content)
		{
			int result;
			using (OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString))
			{
				OleDbCommand cmd = new OleDbCommand(SQLString, connection);
				OleDbParameter myParameter = new OleDbParameter("@content", OleDbType.VarChar);
				myParameter.Value = content;
				cmd.Parameters.Add(myParameter);
				try
				{
					connection.Open();
					int rows = cmd.ExecuteNonQuery();
					result = rows;
				}
				catch (OleDbException E)
				{
					throw new Exception(E.Message);
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}
			return result;
		}
		public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
		{
			int result;
			using (OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString))
			{
				OleDbCommand cmd = new OleDbCommand(strSQL, connection);
				OleDbParameter myParameter = new OleDbParameter("@fs", OleDbType.Binary);
				myParameter.Value = fs;
				cmd.Parameters.Add(myParameter);
				try
				{
					connection.Open();
					int rows = cmd.ExecuteNonQuery();
					result = rows;
				}
				catch (OleDbException E)
				{
					throw new Exception(E.Message);
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}
			return result;
		}
		public static object GetSingle(string SQLString)
		{
			object result;
			using (OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString))
			{
				using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))
				{
					try
					{
						connection.Open();
						object obj = cmd.ExecuteScalar();
						if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
						{
							result = null;
						}
						else
						{
							result = obj;
						}
					}
					catch (OleDbException e)
					{
						connection.Close();
						throw new Exception(e.Message);
					}
				}
			}
			return result;
		}
		public static OleDbDataReader ExecuteReader(string strSQL)
		{
			OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString);
			OleDbCommand cmd = new OleDbCommand(strSQL, connection);
			OleDbDataReader result;
			try
			{
				connection.Open();
				OleDbDataReader myReader = cmd.ExecuteReader();
				result = myReader;
			}
			catch (OleDbException e)
			{
				throw new Exception(e.Message);
			}
			return result;
		}
		public static DataSet Query(string SQLString)
		{
			DataSet result;
			using (OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString))
			{
				DataSet ds = new DataSet();
				try
				{
					connection.Open();
					OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);
					command.Fill(ds, "ds");
				}
				catch (OleDbException ex)
				{
					throw new Exception(ex.Message);
				}
				result = ds;
			}
			return result;
		}
		public static int ExecuteSql(string SQLString, params OleDbParameter[] cmdParms)
		{
			int result;
			using (OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString))
			{
				using (OleDbCommand cmd = new OleDbCommand())
				{
					try
					{
						OleDbHelper.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
						int rows = cmd.ExecuteNonQuery();
						cmd.Parameters.Clear();
						result = rows;
					}
					catch (OleDbException E)
					{
						throw new Exception(E.Message);
					}
				}
			}
			return result;
		}
		public static void ExecuteSqlTran(Hashtable SQLStringList)
		{
			using (OleDbConnection conn = new OleDbConnection(OleDbHelper.connectionString))
			{
				conn.Open();
				using (OleDbTransaction trans = conn.BeginTransaction())
				{
					OleDbCommand cmd = new OleDbCommand();
					try
					{
						foreach (DictionaryEntry myDE in SQLStringList)
						{
							string cmdText = myDE.Key.ToString();
							OleDbParameter[] cmdParms = (OleDbParameter[])myDE.Value;
							OleDbHelper.PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
							int val = cmd.ExecuteNonQuery();
							cmd.Parameters.Clear();
							trans.Commit();
						}
					}
					catch
					{
						trans.Rollback();
						throw;
					}
				}
			}
		}
		public static object GetSingle(string SQLString, params OleDbParameter[] cmdParms)
		{
			object result;
			using (OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString))
			{
				using (OleDbCommand cmd = new OleDbCommand())
				{
					try
					{
						OleDbHelper.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
						object obj = cmd.ExecuteScalar();
						cmd.Parameters.Clear();
						if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
						{
							result = null;
						}
						else
						{
							result = obj;
						}
					}
					catch (OleDbException e)
					{
						throw new Exception(e.Message);
					}
				}
			}
			return result;
		}
		public static OleDbDataReader ExecuteReader(string SQLString, params OleDbParameter[] cmdParms)
		{
			OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString);
			OleDbCommand cmd = new OleDbCommand();
			OleDbDataReader result;
			try
			{
				OleDbHelper.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
				OleDbDataReader myReader = cmd.ExecuteReader();
				cmd.Parameters.Clear();
				result = myReader;
			}
			catch (OleDbException e)
			{
				throw new Exception(e.Message);
			}
			return result;
		}
		public static DataSet Query(string SQLString, params OleDbParameter[] cmdParms)
		{
			DataSet result;
			using (OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString))
			{
				OleDbCommand cmd = new OleDbCommand();
				OleDbHelper.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
				using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
				{
					DataSet ds = new DataSet();
					try
					{
						da.Fill(ds, "ds");
						cmd.Parameters.Clear();
					}
					catch (OleDbException ex)
					{
						throw new Exception(ex.Message);
					}
					result = ds;
				}
			}
			return result;
		}
		private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
		{
			if (conn.State != ConnectionState.Open)
			{
				conn.Open();
			}
			cmd.Connection = conn;
			cmd.CommandText = cmdText;
			if (trans != null)
			{
				cmd.Transaction = trans;
			}
			cmd.CommandType = CommandType.Text;
			if (cmdParms != null)
			{
				for (int i = 0; i < cmdParms.Length; i++)
				{
					OleDbParameter parm = cmdParms[i];
					cmd.Parameters.Add(parm);
				}
			}
		}
		public static OleDbDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
		{
			OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString);
			connection.Open();
			OleDbCommand command = OleDbHelper.BuildQueryCommand(connection, storedProcName, parameters);
			command.CommandType = CommandType.StoredProcedure;
			return command.ExecuteReader();
		}
		public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
		{
			DataSet result;
			using (OleDbConnection connection = new OleDbConnection(OleDbHelper.connectionString))
			{
				DataSet dataSet = new DataSet();
				connection.Open();
				new OleDbDataAdapter
				{
					SelectCommand = OleDbHelper.BuildQueryCommand(connection, storedProcName, parameters)
				}.Fill(dataSet, tableName);
				connection.Close();
				result = dataSet;
			}
			return result;
		}
		private static OleDbCommand BuildQueryCommand(OleDbConnection connection, string storedProcName, IDataParameter[] parameters)
		{
			OleDbCommand command = new OleDbCommand(storedProcName, connection);
			command.CommandType = CommandType.StoredProcedure;
			for (int i = 0; i < parameters.Length; i++)
			{
				OleDbParameter parameter = (OleDbParameter)parameters[i];
				command.Parameters.Add(parameter);
			}
			return command;
		}

    }
}
